﻿namespace ScrumLite.Data
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using ScrumLite.Data;
    using System.Collections;
    using System.Globalization;

    public class RequestStore : IRequestStore
    {
        public Request[] RetrieveCommittedRequests(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<Request> query = (from request in context.Requests
                                             where request.fk_project == projectId
                                             orderby request.en_status ascending, request.priority ascending
                                             where request.committed == true
                                             select request);
                return query.ToArray();
            }
        }

        public Request[] RetrieveOpenedRequests(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<Request> query = (from request in context.Requests
                                             where request.fk_project == projectId
                                             orderby request.committed descending, request.en_status ascending, request.priority ascending
                                             where request.RequestStatus.key == (byte)1 ||
                                                     request.RequestStatus.key == (byte)2 ||
                                                     request.RequestStatus.key == (byte)3
                                             select request);
                return query.ToArray();
            }
        }

        public Request[] RetrieveCompletedRequests(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<Request> query = (from request in context.Requests
                                             where request.fk_project == projectId
                                             where request.RequestStatus.key == (byte)4
                                             where request.completedOn.HasValue
                                             select request);
                return query.ToArray();
            }
        }

        public Request[] RetrieveCommittedCompletedRequests(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<Request> query = (from request in context.Requests
                                             where request.fk_project == projectId
                                             where request.RequestStatus.key == (byte)4
                                             where request.completedOn.HasValue
                                             where request.committed == true
                                             select request);
                return query.ToArray();
            }
        }


        public Request[] RetrieveRequests(Guid projectId, string workAreaName)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<Request> query;
                if (String.IsNullOrEmpty(workAreaName))
                {
                    query = (from request in context.Requests
                             where request.fk_project == projectId
                             orderby request.en_status ascending, request.priority ascending
                             select request);
                }
                else
                {
                    query = (from request in context.RequestWorkAreas
                             where request.Request.fk_project == projectId && 
                             request.WorkArea.name == workAreaName &&
                             request.fk_workarea == request.WorkArea.id &&
                             request.fk_request == request.Request.id
                             orderby request.Request.en_status ascending, request.Request.priority ascending
                             select request.Request);                    
                }
                return query.ToArray();
            }
        }

        public Request RetrieveRequest(Guid requestId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                Request result = context.Requests.SingleOrDefault(r => r.id == requestId);
                return result;
            }
        }

        public Request RetrieveRequest(string projectId, int moniker)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                Request result = context.Requests.SingleOrDefault(r => r.moniker == moniker && r.fk_project == new Guid(projectId));
                return result;
            }
        }

        public WorkArea[] RetrieveWorkAreas(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<WorkArea> areas = (from wa in context.WorkAreas
                                              where wa.fk_project == projectId
                                              select wa);

                return areas.ToArray();
            }
        }

        public WorkArea[] RetrieveWorkAreas(Guid projectId, int moniker)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<WorkArea> areas = (from rwa in context.RequestWorkAreas
                                              where rwa.Request.moniker == moniker && rwa.WorkArea.fk_project == projectId
                                              select rwa.WorkArea);

                return areas.ToArray();
            }
        }

        public void AssignWorkAreas(Guid projectId, int moniker, string[] workAreaIds)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = Console.Out;
                var workAreas = (from wa in context.RequestWorkAreas
                                 where wa.Request.moniker == moniker && wa.WorkArea.fk_project == projectId
                                 select wa);

                context.RequestWorkAreas.DeleteAllOnSubmit(workAreas);
                context.SubmitChanges();
                if (workAreaIds != null)
                {

                    var requestId = (from r in context.Requests
                                     where r.moniker == moniker &&
                                         r.fk_project == projectId
                                     select r.id).SingleOrDefault();

                    var currentWorkAreas = (from w in workAreaIds
                                            select new RequestWorkArea
                                            {
                                                id = Guid.NewGuid(),
                                                fk_workarea = new Guid(w),
                                                fk_request = requestId
                                            });
                    context.RequestWorkAreas.InsertAllOnSubmit(currentWorkAreas);
                    context.SubmitChanges();
                }
            }
        }

        public void SaveWorkArea(WorkArea wa)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = Console.Out;
                var workArea = context.WorkAreas.SingleOrDefault(r => r.id == wa.id);
                if (workArea != null)
                {
                    workArea.name = wa.name;
                    context.SubmitChanges();
                }
                else
                {
                    context.WorkAreas.InsertOnSubmit(wa);
                    context.SubmitChanges();
                }
            }

        }

        public Dictionary<Guid, WorkAreaReport> RetrieveWorkAreasGroupedByWeight(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = Console.Out;

                var pair = from r in context.RequestWorkAreas
                           where r.Request.fk_project == projectId
                           group r by r.WorkArea into g                           
                           select new WorkAreaReport() { Id = g.Key.id, 
                               Name = g.Key.name, 
                               TotalWeight = g.Sum(r => r.Request.weight), 
                               RequestCount = g.Key.RequestWorkAreas.Count , 
                               DeliverableCount = (from q in context.DeliverableWorkAreas
                                   where q.pk_workarea == g.Key.id
                                   select q).Count()
                           };
                
                var notAssigned = from r in context.WorkAreas
                                  where r.fk_project == projectId && pair.Count(tupla => tupla.Id == r.id) == 0
                                  select new WorkAreaReport() { Id = r.id, Name = r.name, TotalWeight = 0, RequestCount = r.RequestWorkAreas.Count, DeliverableCount = r.DeliverableWorkAreas.Count};
                
                var result = pair.Union<WorkAreaReport>(notAssigned).ToDictionary(k => k.Id, v => v);
                return result;
            }
        }

        public Dictionary<WorkArea, Dictionary<byte?, int>> RetrieveRequestsByStatusByWorkArea(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = Console.Out;
                var query = from r in context.RequestWorkAreas
                            where r.WorkArea.fk_project == projectId
                            group r by r.WorkArea into g
                            select
                            new
                            {
                                WorkArea = g.Key,
                                RequestsByStatus = from s in g
                                                   group s by s.Request.en_status into o
                                                   select new
                                                   {
                                                       o.Key,
                                                       RequestsCount = o.Count()
                                                   }
                            };
                return query.ToDictionary(w => w.WorkArea,
                    d => d.RequestsByStatus.ToDictionary(st => st.Key, cnt => cnt.RequestsCount));
            }
        }

        public void Add(Request entity)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = new DebuggerWriter();                
                context.Requests.InsertOnSubmit(entity);
                context.SubmitChanges();
            }
        }

        public void UpdateRequestsPriority(Guid[] requestIds)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                List<Guid> list = new List<Guid>(requestIds);
                context.Log = Console.Out;
                var requests = context.Requests.Where(r => requestIds.Contains(r.id));
                foreach (Request r in requests)
                {
                    int priority = list.FindIndex(m => m == r.id);
                    r.priority = (byte)(priority + 1);
                    context.SubmitChanges();
                }
            }
        }

        public void Update(Request entity, bool updateWorkAreas)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = new DebuggerWriter();
                var original = context.Requests.SingleOrDefault(r => r.id == entity.id);
                if (original != null)
                {
                    if (updateWorkAreas)
                    {
                        // Remove existing workareas associated to the request
                        context.RequestWorkAreas.DeleteAllOnSubmit(original.RequestWorkAreas);

                        System.Data.Linq.EntitySet<RequestWorkArea> requestWorkAreas = new System.Data.Linq.EntitySet<RequestWorkArea>();
                        foreach (RequestWorkArea rwa in entity.RequestWorkAreas)
                        {
                            requestWorkAreas.Add(new RequestWorkArea { id = rwa.id, fk_request = rwa.fk_request, fk_workarea = rwa.fk_workarea });
                        }

                        original.RequestWorkAreas = requestWorkAreas;
                    }

                    original.fk_project = entity.fk_project;
                    original.en_status = entity.en_status;
                    original.title = entity.title;
                    original.moniker = entity.moniker;
                    original.notes = entity.notes;
                    original.priority = entity.priority;
                    if (!string.IsNullOrEmpty(entity.requestor))
                    {
                        original.requestor = entity.requestor;
                    }
                    if (entity.en_status == (byte)4) // Completed
                    {
                        original.order = entity.order;
                        if (!entity.completedOn.HasValue)
                        {
                            original.completedOn = DateTime.Now;
                        }
                    }
                    original.weight = entity.weight;
                    original.committed = entity.committed;
                    original.dueOn = entity.dueOn;
                    context.SubmitChanges();
                }
            }
        }

        public void Delete(Guid requestId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                Request request = context.Requests.SingleOrDefault(r => r.id == requestId);
                context.RequestWorkAreas.DeleteAllOnSubmit(request.RequestWorkAreas);
                context.Requests.DeleteOnSubmit(request);
                context.SubmitChanges();
            }
        }

        public RequestStatus[] RetrieveRequestStatusList()
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<RequestStatus> query = (from requeststatus in context.RequestStatus
                                                   orderby requeststatus.key ascending
                                                   select requeststatus);
                return query.ToArray();
            }
        }

        public int RetrieveLastMoniker(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                var query = from request in context.Requests
                            where request.fk_project == projectId
                            select request.moniker;

                if (query.Count() > 0)
                {
                    return query.Max();
                }
                else
                {
                    return 0;
                }
            }
        }

        public int RetrieveLastOrder(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                int? maxOrder = (from request in context.Requests
                                 where request.fk_project == projectId && request.RequestStatus.key == (byte)4
                                 select request.order).Max();

                if (maxOrder != null)
                {
                    return (int)maxOrder;
                }
                else
                {
                    return 0;
                }
            }
        }

        public Dictionary<byte?, int> RetrieveCountRequestsByStatus(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = Console.Out;
                return (from r in context.Requests
                        where r.fk_project == projectId
                        group r by r.en_status into g
                        select new { g.Key, RequestsCount = g.Count() }).ToDictionary(st => st.Key, c => c.RequestsCount);
            }
        }

        public void CleanCommittedRequestByStatus(Guid projectId, byte[] statusList)
        {

            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                context.Log = Console.Out;
                Request[] requests = context.Requests.Where(r => r.fk_project == projectId &&
                        statusList.Contains(r.en_status.Value)).ToArray();

                foreach (Request r in requests)
                {
                    r.committed = false;
                }
                context.SubmitChanges();
            }
        }

        public int RetrieveRequestsTotalWeight(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<Request> query;
                
                query = (from request in context.Requests
                         where request.fk_project == projectId                         
                         select request);

                int? totalWeight = query.Sum(r => r.weight);
                return totalWeight.HasValue ? (int)totalWeight : 0;
            }
        }

        public int RetrieveCommittedRequestsTotalWeight(Guid projectId)
        {
            using (ScrumLiteDataContext context = new ScrumLiteDataContext())
            {
                IQueryable<Request> query;

                query = (from request in context.Requests
                         where request.fk_project == projectId &&
                         request.committed == true
                         select request);

                int? totalWeight = query.Sum(r => r.weight);
                return totalWeight.HasValue ? (int)totalWeight : 0;
            }
        }
    }
}

